In [139]:
import pymongo
from pymongo import MongoClient
file = open("dbconnect", 'r')
dbConnectionString = str(file.read()).strip()
client = MongoClient(dbConnectionString)
db = client.bridge
collection = db["nbi"]
In [140]:
print("Bridge Records in DB: ", collection.count())
In [ ]:
print('YEAR '+':'+' TOTAL NUMBER OF BRIDGES')
for i in collection.distinct("year"):
print(i, ": ", collection.find({"year":i}).count())
In [52]:
import pandas as pd
columns =["stateCode","deckWidthOutToOut","structureLength","averageDailyTraffic"]
nbi = pd.DataFrame(columns=columns)
#statecodes = ["31","04"]
statecodes = ['25',
'04',
'08',
'38',
'09',
'19',
'26',
'48',
'35',
'17',
'51',
'23',
'16',
'36',
'56',
'29',
'39',
'28',
'11',
'21',
'18',
'06',
'47',
'12',
'24',
'34',
'46',
'13',
'55',
'30',
'54',
'15',
'32',
'37',
'10',
'33',
'44',
'50',
'42',
'05',
'20',
'45',
'22',
'40',
'72',
'41',
'21',
'53',
'01',
'31',
'02',
'49'
]
for i in statecodes:
result = collection.find({"year":2016,"stateCode":i})
temp = pd.DataFrame(list(result))
try:
temp = temp[["stateCode","deckWidthOutToOut","structureLength","averageDailyTraffic"]]
except:
print(i)
pass
df_new = pd.concat([nbi, temp])
nbi = df_new
In [53]:
nbi.count()
Out[53]:
In [63]:
nbi.head()
Out[63]:
In [70]:
stateName = {'25':'MASSACHUSETTS',
'04':'ARIZONA',
'08':'COLORADO',
'38':'NORTH DAKOTA',
'09':'CONNECTICUT',
'19':'IOWA',
'26':'MICHIGAN',
'48':'TEXAS',
'35':'NEW MEXICO',
'17':'ILLINOIS',
'51':'VIRGINIA',
'23':'MAINE',
'16':'IDAHO',
'36':'NEW YORK',
'56':'WYOMING',
'29':'MISSOURI',
'39':'OHIO',
'28':'MISSISSIPI',
'11':'DISTRICT OF COLOMBIA',
'21':'KENTUCKY',
'18':'INDIANA',
'06':'CALIFORNIA',
'47':'TENNESSEE',
'12':'FLORIDA',
'24':'MARYLAND',
'34':'NEW JERSEY',
'46':'SOUTH DAKOTA',
'13':'GEORGIA',
'55':'WISCONSIN',
'30':'MONTANA',
'54':'WEST VIGINIA',
'15':'HAWAII',
'32':'NEVADA',
'37':'NORTH CAROLINA',
'10':'DELAWARE',
'33':'NEW HAMPSHIRE',
'44':'RHODE ISLAND',
'50':'VERMONT',
'42':'PENNSYLVANIA',
'05':'ARKANSAS',
'20':'KANSAS',
'45':'SOUTH CAROLINA',
'22':'LOUISIANA',
'40':'OKLAHOMA',
'72':'PUERTO RICO',
'41':'OREGON',
'21':'MINNESOTA',
'53':'WASHINGTON',
'01':'ALABAMA',
'31':'NEBRASKA',
'02':'ALASKA',
'49':'UTAH'
}
nbi['stateName'] = nbi['stateCode'].map(stateName)
In [83]:
nbi['deckArea']= nbi['deckWidthOutToOut'] * nbi['structureLength']
nbi_summary = nbi[['stateName','deckWidthOutToOut','structureLength','deckArea','averageDailyTraffic']]
nbi_summary.groupby(['stateName']).agg({'stateName':'count',
'deckArea':'sum',
'averageDailyTraffic':'sum'})
#nbi_summary.columns=['Index','States','Total Valid Bridge','Total Deck Area','Total Average Daily Traffic']
Out[83]:
In [81]:
nbi_summary.head()
Out[81]:
In [74]:
nbi.groupby(['stateName'])['stateName'].count()
Out[74]:
In [110]:
import pandas as pd
columns =["maintenanceReponsibility","deckWidthOutToOut","structureLength","averageDailyTraffic"]
nbi2 = pd.DataFrame(columns=columns)
#statecodes = ["31","04"]
statecodes = ['25',
'04',
'08',
'38',
'09',
'19',
'26',
'48',
'35',
'17',
'51',
'23',
'16',
'36',
'56',
'29',
'39',
'28',
'11',
'21',
'18',
'06',
'47',
'12',
'24',
'34',
'46',
'13',
'55',
'30',
'54',
'15',
'32',
'37',
'10',
'33',
'44',
'50',
'42',
'05',
'20',
'45',
'22',
'40',
'72',
'41',
'21',
'53',
'01',
'31',
'02',
'49'
]
for i in statecodes:
result = collection.find({"year":2016,"stateCode":i})
temp2 = pd.DataFrame(list(result))
try:
temp2 = temp2[["maintenanceReponsibility","stateCode","deckWidthOutToOut","structureLength","averageDailyTraffic"]]
except:
print(i)
pass
df_new2 = pd.concat([nbi2, temp2])
nbi2 = df_new2
In [111]:
nbi2.head()
Out[111]:
In [112]:
maintenanceReponsibility ={ -1 : 'NA',
1 : 'State Highway Agency',
21: 'Other State Agency',
4 : 'City or Municipal Highway Agency',
80: 'Unknown',
66: 'National Park Service',
2 : 'County Highway Agency',
60: 'Other Federal Agencies (not listest below)',
64: 'U.S forest Services' ,
68: 'Bureau of Land Management',
26: 'Private (other than railroad)',
62: 'Bureau of Indian Affairs',
3 : 'Town or Township Highway Agency',
25: 'Other Local Agencies',
11: 'State Park, Forest or Reservation Agency',
63: 'Bureau of Fish and Wildlife',
27: 'Railroad',
74: 'Army',
70: 'Corps of Engineers (Civil)',
72: 'Air Force',
61: 'Indian Tribal Agency',
71: 'Corps of Engineers (Military)',
69: 'Bureau of Reclamation',
67: 'Tennesssee Valley Authority',
32: 'Local Toll Authority',
12: 'Local Park, Forest or Reservation Agency',
31: 'State Toll Authority',
73: 'Navy / Marines',
75: 'NASA',
76: 'Metropolitian Washington Airports Service'
}
'''
for i in nbi2['maintenanceReponsibility']:
i = str(i)
nbi2['Maintenance Responsibility'] = maintenanceReponsibility[i]
'''
nbi2['Maintenance Reponsibility'] = nbi2['maintenanceReponsibility'].map(maintenanceReponsibility)
In [113]:
nbi2.head()
Out[113]:
In [114]:
nbi2['deckArea']= nbi2['deckWidthOutToOut'] * nbi2['structureLength']
In [115]:
nbi2.head()
Out[115]:
In [120]:
nbi2_maintenance=nbi2.groupby(['Maintenance Reponsibility']).agg({'Maintenance Reponsibility:'count',
'deckArea':'sum',
'averageDailyTraffic':'sum'})
In [128]:
mant_valid_bridges = nbi2_maintenance['Maintenance Reponsibility'].sum()
mant_sum_deck = nbi2_maintenance['deckArea'].sum()
mant_sum_adt = nbi2_maintenance['averageDailyTraffic'].sum()
In [137]:
nbi2_maintenance['Percent Valid Bridge']= (nbi2_maintenance['Maintenance Reponsibility']/mant_valid_bridges)*100
nbi2_maintenance['Percent Deck Area']= (nbi2_maintenance['deckArea'] / mant_sum_deck)*100
nbi2_maintenance['Percent ADT']= (nbi2_maintenance['averageDailyTraffic'] / mant_sum_adt)*100
In [138]:
nbi2_maintenance
Out[138]: